#!/bin/sh


# daily_duty.sh  
# A-GDS团队值日统计n天系统运行数据bash脚本
# wangyibao@gmail.com  
# date: 2012-06-01

# 命令使用说明  
# 从标准输入读取最近的n天数据
# usage: daily_duty.sh  
# 从指定文件读取最近的n天数据 
# usage: daily_duty.sh config.txt  
# 通过here string指定数据库名称和表名称  
# usage: trunc_db.sh <<<"n"  

# 输入格式说明  
# 由一行或多行组成，可以是下列之一：  
# (1) 空行：将被忽略  
# (2) 注释：以#开头的行，将被忽略  
# (3) 目前只支持整数n，n的最佳设置n>=3 && n <=6，如果超出6，可能由于数据库中数据清理，就得不到了  

# 执行MySQL SQL语句，输出执行结果  
# usage: db_exec <ip> <port> <username> <password> <dbname> <sql>  
db_exec()
{
     mysql -h "$1" -P "$2" -u "$3" -p$4 -s "$5" -e "$6"
}

# 执行Oracle SQL语句，输出执行结果
# uagde: <sql>
o_db_exec()
{
        VALUE=`sqlplus -S  username/password@proxydb <<EOF
        set heading off;
        set pagesize 0;
        set feedback off;
        set verify off;
        set echo off;
        set timing off;
        $1;
        exit
        EOF`
        echo | awk -v VALUE="$VALUE" 'BEGIN{match(VALUE,/[0-9]*$/);print substr(VALUE,RSTART)}'
}

#获取当前时间
now()
{
        date "+%F"
}

#获取n天前时间
#usage: day_before_n_days <n>
day_before_n_days()
{
        date -d "$1 days ago" "+%F"
}

# 波动率统计
cal_rate()
{
        local msg=$(db_exec "ip" "3306" "username" "password" "agds" "select c.model_name, c.threshold_name, c.threshold_statistic, c.threshold_ratio, c.threshold_deviatio from agds_md_version m,a
gds_model_check_instance c where m.id = c.md_version_id and m.status = 1 order by c.id");
        printf "$msg" | awk 'BEGIN {printf "%140s\n%30s %25s %25s %25s %25s\n%140s\n","------------------------------------------------------------------------------------------------------------------------
-----------------------","model_name","threshold_name","threshold_statistic","threshold_ratio","threshold_deviatio","------------------------------------------------------------------------------------------
-----------------------------------------------------" }{printf "%30s %25s %25s %25s %25s\n", $1, $2, $3, $4, $5} END {printf "%140s\n","----------------------------------------------------------------------
-------------------------------------------------------------------------"}'
#        printf "%130s\n" "-------------------------------------------------------------------------------------------------------------------------------------" 
}


# 通用贷款数据统计
# usage:cal_admission <n> <model_name>
cal_admission()
{
        case $2 in
                ali.admission.decision)
                        head_name="AL Credit Loan(1001)"
                        in_do="MdB2bAdmissionInDO"
                        ou_do="MdB2bAdmissionOutDO"
                        ;;
                cbu.order.decision)
                        head_name="CBU Order Loan(1002)"
                        in_do="MdCbuOrderInDO"
                        ou_do="MdCbuOrderOutDO"
                        ;;
                taobao.admission.decision)
                        ou_do="MdTaobaoAdmissionOutDO"
                        ;;
                tmall.admission.desision)
                        head_name="TM Credit Loan(2002)"
                        in_do="MdTmallAdmissionInDO"
                        ou_do="MdTmallAdmissionOutDO"
                        ;;
                tmall.support.decision)
                        head_name="TM Credit Loan(2005)"
                        in_do="MdTmallSupportInDO"
                        ou_do="MdTmallSupportOutDO"
                        ;;
                *)
                        exit 1
        esac
        declare -i loop=$1;
        local headline="-------"
        for((i=1; i<=loop; i=i+1))
        do
        #打印Head
        printf "%s%s%s\n" "$headline" "$head_name" "$headline"
        #打印日期       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "日期"
        printf "  \n"
        #准入数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                # 物流的biz date和dk的不一致，做特殊处理
                 if      [ "$2" = "cbu.order.decision" ]; then
                        j=j-1;
                 fi
                if [ "$id_n_d" ]; then
                        local in_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = '$in_do$id_n_d'")
                        if [ "$in_tb_n_d" ]; then
                                local in_cnt_n_d=$(db_exec "ip" "3306" "username" "password" "agds_result" "select count(*) from $in_tb_n_d")
                                printf "%15s" "$in_cnt_n_d"
                        else
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "准入"
        printf "  \n"
        #不准入数据     
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                 # 物流的biz date和dk的不一致，做特殊处理
                if      [ "$2" = "cbu.order.decision" ]; then
                        j=j-1;
                fi
                if [ "$id_n_d" ]; then
                        local ou_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = '$ou_do$id_n_d'")
                        if [ "$ou_tb_n_d" ]; then
                                local ou_cnt_n_d=$(db_exec "ip" "3306" "username" "password" "agds_result" "select count(*) from $ou_tb_n_d")
                                printf "%15s" "$ou_cnt_n_d"
                        else
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "不准入"
        printf "  \n"
        if      [ "$2" != "ali.admission.decision" -a "$2" != "cbu.order.decision" ]; then
                #授信额度       
                for((i=1; i<=loop; i=i+1))
                do
                        declare -i j=loop-i+1;
                        if [ "$id_n_d" ]; then
                                local in_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = '$in_do$id_n_d'")
                                        printf "%15s" "$in_cnt_n_d"
                                else
                                        printf "%15s" "None"
                                fi
                        else
                                printf "%15s" "None"
                        fi
                done
                printf "\t%-25s" "授信"
                printf "  \n"
        fi
        #打印数据版本时间       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                 # 物流的biz date和dk的不一致，做特殊处理
                if      [ "$2" = "cbu.order.decision" ]; then
                        j=j-1;
                fi
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "数据版本"
        printf "  \n"
}


# 淘宝集市信用贷款数据统计
# usage:cal_tbc <n>
cal_tbc()
        declare -i loop=$1;
        local headline="-------"
        for((i=1; i<=loop; i=i+1))
        do
                headline="$headline-------"
        done
        #打印Head
        printf "%s%s%s\n" "$headline" "$head_name" "$headline"
        #打印日期       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "日期"
        printf "  \n"

        #准入数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                data_version="$(day_before_n_days $j)"
                day=${data_version:8:2}
                if [ $day -ge "24" ]; then
                        real_data_version="${data_version:0:8}24"
                elif [ $day -ge "16" ]; then
                        real_data_version="${data_version:0:8}16"
                elif [ $day -ge "08" ]; then
                        real_data_version="${data_version:0:8}08"
                else
                if [ "$id_n_d" ]; then
                        local in_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = 'MdTaobaoAdmissionInDO$id_n_d'")
                                printf "%15s" "$in_cnt_n_d"
                        else
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "准入"
        printf "  \n"
        #不准入数据     
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                data_version="$(day_before_n_days $j)"
                day=${data_version:8:2}
                if [ $day -ge "24" ]; then
                        real_data_version="${data_version:0:8}24"
                elif [ $day -ge "16" ]; then
                        real_data_version="${data_version:0:8}16"
                elif [ $day -ge "08" ]; then
                        real_data_version="${data_version:0:8}08"
                else
                        real_data_version="${data_version:0:8}01"
                fi
                if [ "$id_n_d" ]; then
                        local ou_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = 'MdTaobaoAdmissionOutDO$id_n_d'")
                        if [ "$ou_tb_n_d" ]; then
                                local ou_cnt_n_d=$(db_exec "ip" "3306" "username" "password" "agds_result" "select count(*) from $ou_tb_n_d")
                                printf "%15s" "$ou_cnt_n_d"
                        else
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "不准入"
        printf "  \n"
        #清退数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                declare -i k=loop-i-1;
                data_version="$(day_before_n_days $j)"
                day=${data_version:8:2}
                if [ $day -ge "24" ]; then
                        real_data_version="${data_version:0:8}24"
                elif [ $day -ge "16" ]; then
                        real_data_version="${data_version:0:8}16"
                elif [ $day -ge "08" ]; then
                        real_data_version="${data_version:0:8}08"
                else
                        real_data_version="${data_version:0:8}01"
                fi
                if [ "$id_n_d" ]; then
                        local ou_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = 'MdTaobaoAdmissionClearDO$id_n_d'")
                        if [ "$ou_tb_n_d" ]; then
                                j=j-1
                                printf "%15s" "$ou_cnt_n_d"
                        else
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "清退"
        printf "  \n"
        #授信额度       
                declare -i j=loop-i+1;
                data_version="$(day_before_n_days $j)"
                day=${data_version:8:2}
                if [ $day -ge "24" ]; then
                        real_data_version="${data_version:0:8}24"
                elif [ $day -ge "16" ]; then
                        real_data_version="${data_version:0:8}16"
                elif [ $day -ge "08" ]; then
                        real_data_version="${data_version:0:8}08"
                else
                        real_data_version="${data_version:0:8}01"
                fi
                if [ "$id_n_d" ]; then
                        local in_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = 'MdTaobaoAdmissionInDO$id_n_d'")
                        if [ "$in_tb_n_d" ]; then
                                local in_cnt_n_d=$(db_exec "ip" "3306" "username" "password" "agds_result" "select sum(credit_amount) from $in_tb_n_d")
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "授信"
        printf "  \n"
        #打印数据版本时间       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "数据版本"
        printf "  \n"
}

# BlockCode统计
# usage:cal_blk <n>
cal_blk()
{
        head_name="  Block Code(2001)  "
        declare -i loop=$1;
        local headline="-------"
        for((i=1; i<=loop; i=i+1))
        do
                headline="$headline-------"
        done
        #打印Head
        printf "%s%s%s\n" "$headline" "$head_name" "$headline"
        #打印日期       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "日期"
        printf "  \n"
        #准入数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                if [ "$id_n_d" ]; then
                        local in_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = 'MdBlockCodeInDO$id_n_d'")
                        if [ "$in_tb_n_d" ]; then
                                local in_cnt_n_d=$(db_exec "ip" "3306" "username" "password" "agds_result" "select count(*) from $in_tb_n_d")
                                printf "%15s" "$in_cnt_n_d"
                        else
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "可支用"
        printf "  \n"
        #不准入数据     
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                if [ "$id_n_d" ]; then
                        local in_tb_n_d=$(db_exec "ip" "3306" "username" "password" "agds" "select table_name from agds_split_mapping where route_key = 'MdBlockCodeOutDO$id_n_d'")
                        if [ "$in_tb_n_d" ]; then
                                local in_cnt_n_d=$(db_exec "ip" "3306" "username" "password" "agds_result" "select count(*) from $in_tb_n_d")
                                printf "%15s" "$in_cnt_n_d"
                        else
                                printf "%15s" "None"
                        fi
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "不可支用"
        printf "  \n"
        #打印数据版本时间       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i+1;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "数据版本"
        printf "  \n"
}

# 催收数据统计
# usage:cal_col <n>
cal_col()
{
        head_name="  Collection Info   "
        declare -i loop=$1;
        local headline="-------"
        for((i=1; i<=loop; i=i+1))
        do
                headline="$headline-------"
        done
        #打印Head
        printf "%s%s%s\n" "$headline" "$head_name" "$headline"
        do
                declare -i j=loop-i;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "日期"
        printf "  \n"
        #入催数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                local in_col=$(o_db_exec "select  count(distinct cust_id) from agds_coll_case_info where this_ic_date>='$(day_before_n_days $j)' and this_ic_date<'$(day_before_n_days $k)'")
                if [ "$in_col" ]; then
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "入催"
        printf "  \n"
        #出催数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                if [ "$ou_col" ]; then
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "出催"
        printf "  \n"
}

# 预警数据统计
# usage:cal_rik <n>
cal_rik()
{
        head_name="  Risk Infomation   "
        declare -i loop=$1;
        do
                headline="$headline-------"
        done
        #打印Head
        printf "%s%s%s\n" "$headline" "$head_name" "$headline"
        #打印日期       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "日期"
        printf "  \n"
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                if [ "$task_1" ]; then
                        printf "%15s" "$task_1"
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "待处理"
        printf "  \n"
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                if [ "$task_2" ]; then
                        printf "%15s" "$task_2"
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "已确认"
        printf "  \n"

        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                if [ "$task_3" ]; then
                        printf "%15s" "$task_3"
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "待审批"
        printf "  \n"

        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "已取消"
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                if [ "$task_5" ]; then
                        printf "%15s" "$task_5"
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "跟进中"
        printf "  \n"

        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                if [ "$task_6" ]; then
                        printf "%15s" "$task_6"
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "处理中"
        printf "  \n"
}

# 黑名单数据统计
# usage:cal_bll <n>
cal_bll()
{
        head_name="  Black List Info   "
        declare -i loop=$1;
        local headline="-------"
        for((i=1; i<=loop; i=i+1))
        do
                headline="$headline-------"
        done
        #打印Head
        printf "%s%s%s\n" "$headline" "$head_name" "$headline"
        #打印日期

        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "日期"
        printf "  \n"

        local array=(1 2 3 4 5 6 7 8 9 10 100 101 102 103 104 200 201 300 301 401)
        declare -i cnt=0
        do
                for((i=1; i<=loop; i=i+1))
                do
                        declare -i j=loop-i;
                        declare -i k=loop-i-1;
                        if [ "$task_1" ]; then
                                printf "%15s" "$task_1"
                        else
                                printf "%15s" "None"
                        fi
                done
                cnt=cnt+1;
                printf "\t%-25s" "$name"
                printf "  \n"
        done
}


# 外部网站数据抓取统计
# usage:cal_internet <n>
cal_internet()
{
        head_name="    DS     Info     "
        declare -i loop=$1;
        local headline="-------"
        for((i=1; i<=loop; i=i+1))
        do
                headline="$headline-------"
        done
        #打印Head
        printf "%s%s%s\n" "$headline" "$head_name" "$headline"
        #打印日期       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                printf "%15s" "$(day_before_n_days $j)"
        done
        printf "\t%-25s" "日期"
        printf "  \n"
        #江苏工商企业在线网统计数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                local in_col=$(db_exec "ip" "3306" "username" "password" "agds" "select count(distinct reg_no) from agds_gs168_cobase_info where biz_date<'$(day_before_n_days $k)'")
                if [ "$in_col" ]; then
                        printf "%15s" "$in_col"
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "江苏工商企业在线网"
        printf "  \n"
        #浙江企业信用网统计数据       
        for((i=1; i<=loop; i=i+1))
        do
                declare -i j=loop-i;
                declare -i k=loop-i-1;
                local ou_col=$(db_exec "ip" "3306" "username" "password" "agds" "select count(distinct card_num) from agds_zjecredit_base_info where gmt_created<'$(day_before_n_days $k)'")
                if [ "$ou_col" ]; then
                        printf "%15s" "$ou_col"
                else
                        printf "%15s" "None"
                fi
        done
        printf "\t%-25s" "浙江企业信用网"
        printf "  \n"
}

cat $1 |
while read n;
do
        if [ "$n" -a "${n:0:1}" != "#" ]; then      # 忽略空行和注释  
                 cal_admission "$n" "ali.admission.decision";
                 printf "\n"
                 cal_admission "$n" "cbu.order.decision";
                 printf "\n"
                 cal_admission "$n" "taobao.admission.decision";
                 printf "\n"
                 cal_admission "$n" "tmall.admission.desision";
                 printf "\n"
                 cal_admission "$n" "tmall.support.decision";
                 printf "\n"
                 cal_blk "$n";
                 printf "\n"
                 cal_col "$n";
                 printf "\n"
                 cal_rik "$n";
                 printf "\n"
                 cal_bll "$n";
                 printf "\n"
                 cal_internet "$n";
                 printf "\n"
                 cal_rate;
        fi
#done &> ~/daily_duty.log
done 2>&1 | tee ~/daily_duty.log

# END. 
                         